﻿/***********************************************************************************************************************************
 *    Name: Users_Orphaned.sql
 *  Author: Frank Figearo — http://www.sqlnerd.me/ — frank@sqlnerd.me
 * Summary: Find users in the current database w/o a server login. Generate 2 T-SQL statements:
 *	- tsql_remove = Attempt to rejoin the user with an existing login of the same name.
 *	- tsql_reunion= Attempt to drop the user (and schema) from the database. DROP SCHEMA will fail if there are any database objects
 *		in that schema.
**/
SELECT u.type_desc, u.name,
	tsql_remove	= N'BEGIN TRY; DROP SCHEMA [' + u.name + N']; END TRY BEGIN CATCH END CATCH; DROP USER [' + u.name + N'];',
	tsql_reunion= N'ALTER USER [' + u.name + N'] WITH LOGIN= [' + u.name + N'];'
  FROM sys.database_principals u LEFT JOIN sys.server_principals l ON (u.sid = l.sid)
  WHERE l.sid IS Null AND ISNULL(u.sid, 0x00) <> 0x00 AND u.type IN ('G','S','U')
  ORDER BY u.type_desc, u.name;
GO
--================================================================================================================================--
